﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Diagnostics;

namespace CarCricketMatchApp
{
    /// <summary>
    /// the color of car, contains ten colors
    /// </summary>
    public class DBHelper
    {
        //create table player(playerName varchar(100) primary key,
        //passengername varchar(100),playStyle varchar(20),
        //innings integer,
        //runs integer,
        //outs integer)


        private MySqlConnection conn;
        private String connectionString = "server=127.0.0.1;user id=root; password=123456; database=carmatch; pooling=false;charset=utf8";
        private void connectionMysql()
        {

            conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();


            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }

        public Player getPlayer(String name)
        {
            Player player = null;
            string sql = "select playerName,passengername,playstyle,innings,runs,outs from player where playerName = '{0}'";
            sql = sql.Replace("{0}", name);

            conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();
               // string sql = "delete from player";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader dataR = cmd.ExecuteReader();
                while (dataR.Read())
                {

                    try
                    {
                        string playname = dataR[0] as string;
                        string passNa = dataR[1] as string;
                        string playstyle = dataR[2] as string;
                        int inns = (Int32)dataR[3];
                        int runs = (Int32)(dataR[4]);
                        int outs = (Int32)(dataR[5]);
                        player = new Player("");
                        player.Inningscount = inns;
                        player.LastScore = runs;
                        player.OutCount = outs;
                        
                        break;
                    }
                    catch (System.Exception ex)
                    {
                        Debug.Print("DBHelper.cs line 77" + ex.ToString());
                    }
                

                }
                return player;

            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
            }
            return null;
        }

        public int updatePlayer(Player player)
        {

            conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();

                string sql = "update player set passengername=?passengname,playstyle = ?playstyle,innings = ?innings,runs = ?runns,outs =?outs where playerName = ?playname";
                MySqlParameter[] parameters = new MySqlParameter[6];
                parameters[0] = new MySqlParameter("?playname", MySqlDbType.VarChar, 100);
                parameters[0].Value = player.PlayerName;

                parameters[1] = new MySqlParameter("?passengname", MySqlDbType.VarChar, 100);
                parameters[1].Value = player.OwnTeam.Name;


                parameters[2] = new MySqlParameter("?playstyle", MySqlDbType.VarChar, 20);
                parameters[2].Value = player.Style.ToString();

                parameters[3] = new MySqlParameter("?innings", MySqlDbType.Int32);
                parameters[3].Value = player.Inningscount;

                parameters[4] = new MySqlParameter("?runns", MySqlDbType.Int32);
                parameters[4].Value = player.LastScore;

                parameters[5] = new MySqlParameter("?outs", MySqlDbType.Int32);
                parameters[5].Value = player.OutCount;


                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"select LAST_INSERT_ID()";
                int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                return value;
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        public int deleteAllPlayer()
        {
            conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();
                string sql = "delete from player";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"select LAST_INSERT_ID()";
                int value = Int32.Parse(cmd.ExecuteScalar().ToString());

                return value;
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
            }
            return 0;
        }

        

        public int insertPlayer(Player player)
        {
            conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();

                string sql = "insert into player(playerName,passengername,playstyle,innings,runs,outs)values(?playname,?passengname,?playstyle,?innings,?runns,?outs)";

                MySqlParameter[] parameters = new MySqlParameter[6];
                parameters[0] = new MySqlParameter("?playname", MySqlDbType.VarChar, 100);
                parameters[0].Value = player.PlayerName;

                parameters[1] = new MySqlParameter("?passengname", MySqlDbType.VarChar, 100);
                parameters[1].Value = player.OwnTeam.Name;


                parameters[2] = new MySqlParameter("?playstyle", MySqlDbType.VarChar, 20);
                parameters[2].Value = player.Style.ToString();

                parameters[3] = new MySqlParameter("?innings", MySqlDbType.Int32);
                parameters[3].Value = player.Inningscount;

                parameters[4] = new MySqlParameter("?runns", MySqlDbType.Int32);
                parameters[4].Value = player.LastScore;

                parameters[5] = new MySqlParameter("?outs", MySqlDbType.Int32);
                parameters[5].Value = player.OutCount;


                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"select LAST_INSERT_ID()";
                int value = Int32.Parse(cmd.ExecuteScalar().ToString());

                return value;
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
            }
            return 0;
        }
    }
}
